from django.shortcuts import render
from django.shortcuts import render_to_response
from django.http import HttpResponseRedirect
from django.core.context_processors import csrf
from forms import SearchHotel
from django.contrib import messages

from django.db import connection
from models import Hotel, Room, RoomImage
from reservation.models import Reservation

def search(request):
    #city = Hotel.objects.values_list('city', flat=True).distinct()
    #region = Hotel.objects.values_list('region').distinct()
    #return render_to_response("search.html",{'city': city, 'region': region})

    if request.method == "POST":
        form = SearchHotel(request.POST)

        if form.is_valid():
            hotel = form.cleaned_data.get('hotel', '')
            city = form.cleaned_data.get('city', '')
            region = form.cleaned_data.get('region', '')

            checkin = form.cleaned_data.get('checkin', '')
            checkout = form.cleaned_data.get('checkout', '')

            checkin = checkin.strftime('%Y-%d-%m')
            checkout = checkout.strftime('%Y-%d-%m')

            request.session['checkin'] = checkin
            request.session['checkout'] = checkout

            rooms = available_rooms(hotel, city, region, checkin, checkout)

            return render_to_response("search.html", {'rooms': rooms, 'user': request.user})
        else:
            messages.error(request, "Error")

    args = {}
    args.update(csrf(request))
    args['form'] = SearchHotel()
    args.update({'user': request.user})
    return render_to_response("search.html", args)


def rooms(request, room_id):
    rooms = room_details(room_id)
    return render_to_response("rooms.html", {'rooms': rooms, 'user': request.user})


def available_rooms(hotel, city, region, checkin, checkout):
    cursor = connection.cursor()

    sql = "SELECT * FROM hotels_room WHERE hotel_id = ( SELECT id FROM hotels_hotel WHERE name='%s' AND city='%s' AND region='%s' ) AND id NOT IN ( SELECT room_id FROM reservation_reservation WHERE check_in BETWEEN '%s' AND '%s' OR check_out BETWEEN '%s' AND '%s' ) OR number_rooms > (SELECT COUNT(*) FROM reservation_reservation WHERE room_id= hotels_room.id)" %(hotel, city, region, checkin, checkout, checkin, checkout)

    cursor.execute(sql)

    rows = cursor.fetchall()

    return rows

def room_details(room_id):
    cursor = connection.cursor()

    sql = "SELECT * FROM hotels_room INNER JOIN hotels_roomimage ON hotels_room.id=hotels_roomimage.room_id  WHERE hotels_room.id='%s' "  %(room_id)

    cursor.execute(sql)

    rows = cursor.fetchall()

    return rows

